package cn.com.lgyw.jdbc.util;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Vector;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import cn.com.lgyw.jdbc.bean.ConnectionBean;
import cn.com.lgyw.jdbc.transaction.TransactionCallBack;
import cn.com.lgyw.jdbc.transaction.TransactionUtil;

/**
 * @author guojian
 *
 */
public class JdbcTemplate {
	public static final String SQL_REGEX = "\\[(.*?)\\]";

	/**
	 * 根据fields来查询数据
	 * 
	 * @param sql
	 * @param c
	 * @return List<T>
	 */
	public static <T> List<T> queryListInfoByFields(ConnectionBean con, String sql, Class<T> c, Object... fields) {
		return JdbcUtil.buildSqlForListBean(con, sql, c, Arrays.asList(fields), con.getTypeName());
	}

	public static <T> List<T> queryListInfoByFields(String dbName, String sql, Class<T> c, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryListInfoByFields(conBean, sql, c, fields);
	}

	/**
	 * 根据对象bean来查询数据
	 * 
	 * @param sql
	 * @param c
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static <T> List<T> queryListInfoByBean(ConnectionBean con, String sql, Class<T> c, Object bean) {
		Matcher matcher = Pattern.compile(JdbcTemplate.SQL_REGEX).matcher(sql);
		List<Object> fields = JdbcUtil.formatBeanToFields(bean, matcher);
		sql = matcher.replaceAll("?");
		return JdbcUtil.buildSqlForListBean(con, sql, c, fields, con.getTypeName());
	}

	public static <T> List<T> queryListInfoByBean(String dbName, String sql, Class<T> c, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryListInfoByBean(conBean, sql, c, bean);
	}

	public static <T> T queryObjectInfoByFields(ConnectionBean con, String sql, Class<T> c, Object... fields) {
		List<T> list = queryListInfoByFields(con, sql, c, fields);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public static <T> T queryObjectInfoByFields(String dbName, String sql, Class<T> c, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryObjectInfoByFields(conBean, sql, c, fields);
	}

	public static <T> T queryObjectInfoByBean(ConnectionBean con, String sql, Class<T> c, Object bean) {
		List<T> list = queryListInfoByBean(con, sql, c, bean);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return null;
	}

	public static <T> T queryObjectInfoByBean(String dbName, String sql, Class<T> c, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryObjectInfoByBean(conBean, sql, c, bean);
	}

	public static List<Object[]> queryListFieldInfoByFields(ConnectionBean con, String sql, Object... fields) {
		return JdbcUtil.buildSqlForListField(con, sql, Arrays.asList(fields), con.getTypeName());
	}

	public static List<Object[]> queryListFieldInfoByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryListFieldInfoByFields(conBean, sql, fields);
	}

	public static Vector<Object> queryVectorFieldInfoByFields(ConnectionBean con, String sql, Object... fields) {
		return JdbcUtil.buildSqlForVectorField(con, sql, Arrays.asList(fields), con.getTypeName());
	}

	// 得到所有的列名
	public static Vector<String> getTableColumnNameBySql(ConnectionBean con, String sql) {
		return JdbcUtil.getTableColumnNameBySql(con, sql, con.getTypeName());
	}

	public static <T> List<Object[]> queryListFieldInfoByBean(ConnectionBean con, String sql, Object bean) {
		Matcher matcher = Pattern.compile(JdbcTemplate.SQL_REGEX).matcher(sql);
		List<Object> fields = JdbcUtil.formatBeanToFields(bean, matcher);
		sql = matcher.replaceAll("?");
		return JdbcUtil.buildSqlForListField(con, sql, fields, con.getTypeName());
	}

	public static <T> List<Object[]> queryListFieldInfoByBean(String dbName, String sql, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryListFieldInfoByBean(conBean, sql, bean);
	}

	public static Object[] queryFieldInfoByFields(ConnectionBean con, String sql, Object... fields) {
		List<Object[]> list = JdbcUtil.buildSqlForListField(con, sql, Arrays.asList(fields), con.getTypeName());
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return Collections.emptyList().toArray();
	}

	public static Object[] queryFieldInfoByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryFieldInfoByFields(conBean, sql, fields);
	}

	public static Object[] queryFieldInfoByBean(ConnectionBean con, String sql, Object bean) {
		List<Object[]> list = queryListFieldInfoByBean(con, sql, bean);
		if (list != null && list.size() > 0) {
			return list.get(0);
		}
		return Collections.emptyList().toArray();
	}

	public static Object[] queryFieldInfoByBean(String dbName, String sql, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryFieldInfoByBean(conBean, sql, bean);
	}

	public static boolean updateByBean(ConnectionBean con, String sql, Object bean) {
		Matcher matcher = Pattern.compile(JdbcTemplate.SQL_REGEX).matcher(sql);
		List<Object> fields = JdbcUtil.formatBeanToFields(bean, matcher);
		sql = matcher.replaceAll("?");
		return JdbcUtil.executeUpdate(con, sql, fields, con.getTypeName());
	}

	public static boolean updateByBean(String dbName, String sql, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return updateByBean(conBean, sql, bean);
	}

	public static boolean updateByFields(ConnectionBean con, String sql, Object... fields) {
		return JdbcUtil.executeUpdate(con, sql, Arrays.asList(fields), con.getTypeName());
	}

	public static boolean updateByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return updateByFields(conBean, sql, fields);
	}

	public static void updateByFieldsForTransaction(ConnectionBean con, String sql, Object... fields) throws SQLException {
		Connection connection = con.getCon();
		if (connection.getAutoCommit()) {
			connection.setAutoCommit(false);
		}
		List<ConnectionBean> conBeans = TransactionUtil.connectionBeanThread.get();
		if (conBeans == null) {
			conBeans = new ArrayList<ConnectionBean>();
			conBeans.add(con);
			TransactionUtil.connectionBeanThread.set(conBeans);
		} else {
			if (!conBeans.contains(con)) {
				conBeans.add(con);
			}
		}
		JdbcUtil.executeUpdateForTransaction(con.getCon(), sql, Arrays.asList(fields));
	}

	public static boolean insertByBean(ConnectionBean con, String sql, Object bean) {
		Matcher matcher = Pattern.compile(JdbcTemplate.SQL_REGEX).matcher(sql);
		List<Object> fields = JdbcUtil.formatBeanToFields(bean, matcher);
		sql = matcher.replaceAll("?");
		return JdbcUtil.executeUpdate(con, sql, fields, con.getTypeName());
	}

	public static boolean insertByBean(String dbName, String sql, Object bean) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return insertByBean(conBean, sql, bean);
	}

	public static boolean insertByFields(ConnectionBean con, String sql, Object... fields) {
		return JdbcUtil.executeUpdate(con, sql, Arrays.asList(fields), con.getTypeName());
	}

	public static boolean insertByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return insertByFields(conBean, sql, fields);
	}

	public static List<Map<String, Object>> queryListMapByFields(ConnectionBean con, String sql, Object... fields) {
		return JdbcUtil.buildSqlForMapField(con, sql, Arrays.asList(fields), con.getTypeName());
	}

	public static List<Map<String, Object>> queryListMapByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryListMapByFields(conBean, sql, fields);
	}

	public static Map<String, Object> queryMapByFields(ConnectionBean con, String sql, Object... fields) {
		List<Map<String, Object>> mapList = queryListMapByFields(con, sql, fields);
		if (mapList != null && mapList.size() > 0) {
			return mapList.get(0);
		}
		return Collections.emptyMap();
	}

	public static Map<String, Object> queryMapByFields(String dbName, String sql, Object... fields) {
		ConnectionBean conBean = DbConUtil.getConnectionBean(dbName);
		return queryMapByFields(conBean, sql, fields);
	}

	/**
	 * 执行JDBC事务操作
	 */
	public static void executeJDBCTransactionOperation(ConnectionBean con, TransactionCallBack tc) {
		JdbcUtil.beginJdbcTransaction(con.getCon(), tc, con.getTypeName());
	}

}
